package com.novoda.runbuddy.util;

import java.io.FileNotFoundException;
import java.util.Calendar;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.location.Location;
import android.util.Log;

import com.novoda.runbuddy.model.Jog;
import com.novoda.runbuddy.model.ListOfJogs;
import com.novoda.runbuddy.model.ListOfRuns;
import com.novoda.runbuddy.model.Route;
import com.novoda.runbuddy.model.Run;
import com.novoda.runbuddy.provider.JogsDBSQL;
import com.novoda.runbuddy.provider.RouteDBSQL;
import com.novoda.runbuddy.provider.RunDBSQL;
import com.novoda.runbuddy.model.RunSegment;

public class DBHelperImpl2 implements IDBHelper {

	private SQLiteDatabase		db;
	private static final String	TAG	= "DBHelper";
	private android.database.sqlite.SQLiteDatabase.CursorFactory CursorFactory = null;

	public DBHelperImpl2() {

	}

	public DBHelperImpl2(Context ctx) {
		db = ctx.openOrCreateDatabase(JogsDBSQL.DATABASE_NAME, 0, CursorFactory);
	}

	public DBHelperImpl2(SQLiteDatabase db) {
		this.db = db;
	}

	public void createJoggingEntriesDB(Context ctx) {
		db = ctx.openOrCreateDatabase(JogsDBSQL.DATABASE_NAME, 0, CursorFactory);
		db.execSQL(JogsDBSQL.CREATE_JOG_TABLE);
		db.execSQL(JogsDBSQL.CREATE_ROUTE_TABLE);
		db.execSQL(JogsDBSQL.CREATE_ROUTE_POINT_TABLE);
		db.execSQL(JogsDBSQL.CREATE_TIME_TABLE);
		Log.i(TAG, "Created new JoggingEntries DB");
	}

	public void close() {
		db.close();
	}

	/*
	 * Add all of the points of a jog to a jogroute and then add the
	 * corresponding times Add to Jogs table Add to route table Add all of the
	 * points of a JogRoute Add all the corresponding times of the point reached
	 * 
	 */
	public void addJogEntry(Jog entry) {

		db.execSQL(JogsDBSQL.insertJogName(entry.name));

		Cursor c = db.rawQuery(JogsDBSQL.selectJustEnteredJogId(), null);
		c.moveToFirst();
		String jogId = c.getString(JogsDBSQL.JOGRESULTS_JOG_ID_COL);
		String justEnteredJogId = new String(jogId);

		db.execSQL(JogsDBSQL.insertRouteJogId(justEnteredJogId));
		Cursor c2 = db.rawQuery(JogsDBSQL.selectJustEnteredRouteId(justEnteredJogId), null);
		c2.moveToFirst();
		String justEnteredRouteId = c2.getString(0);

		Map<Long, Location> locationsAndTimes = entry.getlocationsAndTimes();
		int routePointNo = 1;

		for (Iterator<Map.Entry<Long, Location>> it = locationsAndTimes.entrySet().iterator(); it.hasNext();) {

			Map.Entry<Long, Location> locationOnRun = (Map.Entry<Long, Location>) it.next();
			Location location = locationOnRun.getValue();

			// Log.w(TAG, "Adding route point to db " + location.getLatitude() +
			// ", " + location.getLongitude());

			db.execSQL(JogsDBSQL.insertRoutePoint(Integer.toString(routePointNo), justEnteredRouteId, Integer
					.toString((int) (location.getLongitude() * 1000000)), Integer
					.toString((int) (location.getLatitude() * 1000000))));
			Cursor routePoint1 = db.rawQuery(JogsDBSQL.selectJustEnteredRoutePointId(justEnteredRouteId), null);
			routePoint1.moveToFirst();

			db.execSQL(JogsDBSQL.addATimeEntry(justEnteredJogId, justEnteredRouteId, routePoint1.getString(0), DBHelperTools
					.isoFormat(locationOnRun.getKey()), "FALSE"));
			routePointNo++;
		}

	}

	// insertJogEntryBasic simply creates the jog and route in the database
	public long getNewJogId(String name) {
		db.execSQL(JogsDBSQL.insertJogName(name));

		Cursor c = db.rawQuery(JogsDBSQL.selectJustEnteredJogId(), null);
		c.moveToFirst();
		long jogId = c.getLong(JogsDBSQL.JOGRESULTS_JOG_ID_COL);

		return jogId;

	}

	public long getNewRouteId(long jogId) {
		db.execSQL(JogsDBSQL.insertRouteJogId(Long.toString(jogId)));
		Cursor c2 = db.rawQuery(JogsDBSQL.selectJustEnteredRouteId(Long.toString(jogId)), null);
		c2.moveToFirst();
		long justEnteredRouteId = c2.getLong(0);
		return justEnteredRouteId;
	}

	public void insertRoutePoint(long jogId, long routeId, int routePointNo, Location point) {
		db.execSQL(JogsDBSQL.insertRoutePoint(Integer.toString(routePointNo), Long.toString(routeId), Integer.toString((int) (point
				.getLongitude() * 1000000)), Integer.toString((int) (point.getLatitude() * 1000000))));
		Cursor routePoint1 = db.rawQuery(JogsDBSQL.selectJustEnteredRoutePointId(Long.toString(routeId)), null);
		routePoint1.moveToFirst();

		db.execSQL(JogsDBSQL.addATimeEntry(Long.toString(jogId), Long.toString(routeId), routePoint1.getString(0), DBHelperTools
				.isoFormat(point.getTime()), "FALSE"));
		routePointNo++;

	}

	public void updateJogEntry(long Id, Jog entry) {
		ContentValues args = new ContentValues();
		args.put(JogsDBSQL.JOG_ID, entry.get_id());
		args.put(JogsDBSQL.JOG_NAME, entry.getName());
		// args.put(JogsDBSQL.JOG_START_TIME, entry.getStartTime());
		// args.put(JogsDBSQL.JOG_STOP_TIME, entry.getStopTime());

		db.update(JogsDBSQL.JOGGING_ENTRIES_TABLE, args, "id=" + Id, null);
	}

	public void deleteJogEntry(long Id) {
		db.delete(JogsDBSQL.JOGGING_ENTRIES_TABLE, "id=" + Id, null);
	}

	public ListOfRuns fetchAllRunEntries() {
		ListOfRuns ret = new ListOfRuns();
		Cursor c = db.query(RunDBSQL.RUNNING_ENTRIES_TABLE, RunDBSQL.COLUMNS_IN_RUN_ENTRY, null, null, null, null, null);
		Cursor c2 = db.query(RunDBSQL.RUNSEGMENT_ENTRIES_TABLE, RunDBSQL.COLUMNS_IN_RUNSEGMENT_ENTRY, null, null, null, null, null);
		
		int numRows = c.getCount();
		c.moveToFirst();
		for (int i = 0; i < numRows; ++i) {
			
			int runId = c.getInt(0);
			int routeId = c.getInt(1);
			String runName = c.getString(2);
			long runTotalTime = c.getLong(3);
			long runCreationTime = c.getLong(4);
			int geoId = 0;
			int route_sid = 0;
			
			LinkedList<RunSegment> runSegments = new LinkedList<RunSegment>();
			
			
			//for the number of runSegments for the run
			//add the run Segments to the list

			
			Route route = new Route(routeId, route_sid, geoId, runName, null, runCreationTime);
			
			ret.add(new Run(runId, route, runName, runSegments, runTotalTime, runCreationTime));
			c.moveToNext();
		}

		return ret;
	}

	public ListOfJogs fetchAllTodaysJogEntries() {
		Cursor c = db.rawQuery(JogsDBSQL.FETCH_ALL_TODAYS_JOG_ENTRIES, null);
		return getJogsFromCursor(c);
	}

	public ListOfJogs fetchAllJogEntries(Integer day, Integer month, Integer year) {
		Cursor c = db.rawQuery(JogsDBSQL.GET_ENTRIES(day, month, year), null);
		return getJogsFromCursor(c);
	}
	
	public ListOfRuns fetchAllRunEntries(Integer day, Integer month, Integer year) {
		Cursor c = db.rawQuery(RunDBSQL.GET_ENTRIES(day, month, year), null);
		return getRunsFromCursor(c);
	}

	private ListOfRuns getRunsFromCursor(Cursor c) {
		// TODO Auto-generated method stub
		return null;
	}

	public ListOfJogs fetchAllThisMonthsJogEntries(Calendar calendar) {
		Cursor c = db.rawQuery(JogsDBSQL.GET_MONTH_ENTRIES((calendar.get(Calendar.MONTH) + 1), calendar.get(Calendar.YEAR)), null);
		return getJogsFromCursor(c);
	}

	private ListOfJogs getJogsFromCursor(Cursor c) {
		int newJogId = 0;
		int lastRowEnteredJogId = 0;
		String lastEnteredJogName = "";
		String lastEnteredJogId = "";

		ListOfJogs ret = new ListOfJogs();
		TreeMap<Long, Location> jogTimes = new TreeMap<Long, Location>();
		int numRows = c.getCount();
		c.moveToFirst();

		for (int i = 0; i < numRows; ++i) {
			newJogId = c.getInt(JogsDBSQL.JOGRESULTS_JOG_ID_COL);

			if (newJogId == lastRowEnteredJogId) {
				jogTimes.put(DBHelperTools.expectedFormat(c.getString(JogsDBSQL.JOGRESULTS_TIME_COL)), getALocation(JogsDBSQL.JOGRESULTS_LATITUDE_COL, JogsDBSQL.JOGRESULTS_LONGITUDE_COL, c));
			} else {

				if (jogTimes.size() > 0) {
					ret.add(new Jog(lastEnteredJogName, lastEnteredJogId, jogTimes));
					jogTimes = new TreeMap<Long, Location>();
				}

				jogTimes.put(DBHelperTools.expectedFormat(c.getString(JogsDBSQL.JOGRESULTS_TIME_COL)), getALocation(JogsDBSQL.JOGRESULTS_LATITUDE_COL, JogsDBSQL.JOGRESULTS_LONGITUDE_COL, c));
			}

			lastRowEnteredJogId = newJogId;
			lastEnteredJogName = c.getString(JogsDBSQL.JOGRESULTS_NAME_COL);
			lastEnteredJogId = c.getString(JogsDBSQL.JOGRESULTS_ROUTE_ID_COL);

			if (c.isLast()) {
				Jog newJog = new Jog(c.getString(JogsDBSQL.JOGRESULTS_NAME_COL), c.getString(JogsDBSQL.JOGRESULTS_ROUTE_ID_COL),	jogTimes);
				newJog.set_id(newJogId);
				ret.add(newJog);
			}

			c.moveToNext();

		}
		return ret;
	}

	private Location getALocation(int LONGITUDE_COL, int LATITUDE_COL, Cursor c) {
		Location pointLocation = new Location("location");
		pointLocation.setLongitude(c.getInt(LONGITUDE_COL));
		pointLocation.setLatitude(c.getInt(LATITUDE_COL));
		return pointLocation;
	}

	public Jog fetchJogEntry(String id) {
		Jog row = new Jog();

		Cursor c = db.rawQuery(JogsDBSQL.selectAJogEntry(id), null);

		if (c.getCount() > 0) {
			c.moveToFirst();
			row.set_id(c.getLong(0));
			row.setName(c.getString(1));
			row.setStartTime(c.getLong(2));
			row.setStopTime(c.getLong(3));

			return row;
		} else {
			row.set_id(-1);
			row.setName(null);
		}
		return row;
	}

	public Jog fetchFullJogEntry(String id) {
		Cursor c = db.rawQuery(JogsDBSQL.selectAJogEntry(id), null);

		ListOfJogs jogList = getJogsFromCursor(c);

		if (jogList.isEmpty())
			return null;
		return jogList.getFirst();
	}

	public Cursor getCursorOfAllJogs() {
		return db.query(JogsDBSQL.JOGGING_ENTRIES_TABLE, JogsDBSQL.COLUMNS_IN_JOG_ENTRY, null, null, null, null, null);
	}

	public ListOfJogs fetchAllJogEntries() {
		// TODO Auto-generated method stub
		return null;
	}

}
